package dataBase;

import java.awt.Color;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;

import core.Constants;
import core.Person;

public class DataBase extends GeneralDB {

	private int [] getAllArchersInDBBuffer;
	private boolean getAllArchersInDBBufferIsUpToDate = false;

	private Person [] getAllArchersInDBQuickBuffer;
	private boolean getAllArchersInDBQuickBufferIsUpToDate = false;

	private HashMap < Integer, Person > getAllArchersBuffer;
	private boolean getAllArchersBufferIsUpToDate = false;

	public DataBase(String dbName) throws SQLException {
		super(dbName);
	}

	private void setUpToDate(boolean isUpToDate) {
		System.out.println("setuptodate: " + isUpToDate);
		this.getAllArchersInDBBufferIsUpToDate = isUpToDate;
		this.getAllArchersInDBQuickBufferIsUpToDate = isUpToDate;
		this.getAllArchersBufferIsUpToDate = isUpToDate;
	}

	/**
	 * Creates the archer SQL-Table
	 */
	public void createArcherTable() {

		try {
			this.statement
					.execute("create table "
							+ Constants.ARCHER_TABLENAME
							+ "(id int, name varchar(40), surName varchar(40), nation varchar(40), country varchar(40), bowCathegory varchar(40), ageClass varchar(40), club varchar(40), gender varchar(40), license varchar(40), countryRating varchar(40), birthDate varchar(40))");
			this.insertArcher(Constants.DUMMY_PERSON);
		} catch (SQLException e) {
			// e.printStackTrace();
		}
	}

	/**
	 * SQL drop table archer
	 * @throws SQLException
	 */
	public void deleteArcherTable() throws SQLException {
		super.deleteTable(Constants.ARCHER_TABLENAME);
	}

	/**
	 * @param person
	 *            the person to be checked
	 * @return true if person is already in the DB, false otherwise
	 */
	public boolean personExistsAlready(Person person) {
		return this.getAllArchers().containsValue(person);
	}

	/**
	 * @return the number of persons stored in the DB
	 * @throws SQLException
	 */
	public int getNumberOfEntries() throws SQLException {
		return this.getAllArchers().size();
	}

	/**
	 * @param person
	 *            the person who's id you're looking for
	 * @return the person's id if found in the DB, -1 otherwise
	 * @throws SQLException
	 */
	public int getArcherID(Person person) throws SQLException {
		if (!this.getAllArchersBufferIsUpToDate) {
			this.getAllArchers();
		}

		if (this.getAllArchersBuffer.containsValue(person)) {
			for (int i = 1; i <= this.getNumberOfEntries(); i++) {
				if (this.getAllArchersBuffer.get(i).equals(person)) {
					return i;
				}
			}
			return -1;

		} else {
			return -1;
		}
	}

	/**
	 * @return the ids of all archers in the database
	 */
	public int [] getAllArchersInDB() {
		if (!this.getAllArchersInDBBufferIsUpToDate) {

			LinkedList < Integer > tmp = new LinkedList < Integer >();
			int entries = 0;
			try {
				this.resultSet = this.statement.executeQuery("SELECT id FROM " + Constants.ARCHER_TABLENAME);
				while (this.resultSet.next()) {
					tmp.add(this.resultSet.getInt(1));
					entries++;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			this.getAllArchersInDBBuffer = new int [entries];
			for (int i = 0; i < entries; i++) {
				this.getAllArchersInDBBuffer [i] = tmp.removeFirst();
			}
			this.getAllArchersInDBBufferIsUpToDate = true;
		}
		return this.getAllArchersInDBBuffer;
	}

	/**
	 * @return all players in DB. Can contain array slots with null. The
	 *         player's ID is their index in the array
	 * @throws SQLException
	 */
	public Person [] getAllArchersInDBQuick() throws SQLException {
		if (!this.getAllArchersInDBQuickBufferIsUpToDate) {
			/*
			 * checks for the next unique id
			 */
			this.resultSet = this.statement.executeQuery("SELECT id FROM " + Constants.ARCHER_TABLENAME + " ORDER BY id DESC");
			int id = 1;
			if (this.resultSet.next()) {
				id = this.resultSet.getInt(1) + 1;
			}
			this.getAllArchersInDBQuickBuffer = new Person [id];
			for (int i = 0; i < this.getAllArchersInDBQuickBuffer.length; i++) {
				this.getAllArchersInDBQuickBuffer [i] = null;
			}

			try {
				this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.ARCHER_TABLENAME);

				while (this.resultSet.next()) {
					String name = this.resultSet.getString(2);
					String surName = this.resultSet.getString(3);
					String nation = this.resultSet.getString(4);
					String country = this.resultSet.getString(5);
					String bowCathegory = this.resultSet.getString(6);
					String ageClass = this.resultSet.getString(7);
					String club = this.resultSet.getString(8);
					String gender = this.resultSet.getString(9);
					boolean license = this.resultSet.getBoolean(10);
					boolean countryRating = this.resultSet.getBoolean(11);
					String birthdate = this.resultSet.getString(12);

					this.getAllArchersInDBQuickBuffer [this.resultSet.getInt(1)] = new Person(name, surName, nation, country, bowCathegory, ageClass, club, gender, license, countryRating, birthdate);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			this.getAllArchersInDBQuickBufferIsUpToDate = true;
		}
		return this.getAllArchersInDBQuickBuffer;
	}

	/**
	 * @return a HashMap of all Persons in the DB with their unique ID as the
	 *         key
	 */
	public HashMap < Integer, Person > getAllArchers() {
		if (!this.getAllArchersBufferIsUpToDate) {
			this.getAllArchersBuffer = new HashMap < Integer, Person >();

			try {
				this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.ARCHER_TABLENAME + " ORDER BY id");
				while (this.resultSet.next()) {
					this.getAllArchersBuffer
							.put(this.resultSet.getInt(1),
									new Person(this.resultSet.getString(2), this.resultSet.getString(3), this.resultSet.getString(4), this.resultSet.getString(5), this.resultSet.getString(6),
											this.resultSet.getString(7), this.resultSet.getString(8), this.resultSet.getString(9), this.resultSet.getBoolean(10), this.resultSet.getBoolean(11),
											this.resultSet.getString(12)));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			this.getAllArchersBufferIsUpToDate = true;
		}
		return this.getAllArchersBuffer;
	}

	/**
	 * @param id
	 *            the unique DB ID
	 * @return the person under the unique ID
	 */
	public Person getById(int id) {
		if (!this.getAllArchersBufferIsUpToDate) {
			this.getAllArchers();
		}
		return this.getAllArchersBuffer.get(id);
		// Person toReturn = new Person();
		//
		// try {
		// this.resultSet = this.statement.executeQuery("SELECT * FROM " +
		// Constants.ARCHER_TABLENAME + " where id=" + id);
		// if (this.resultSet.next()) {
		//
		// toReturn.setName(this.resultSet.getString(2));
		// toReturn.setSurName(this.resultSet.getString(3));
		// toReturn.setNation(this.resultSet.getString(4));
		// toReturn.setCountry(this.resultSet.getString(5));
		// toReturn.setBowCathegory(this.resultSet.getString(6));
		// toReturn.setAgeClass(this.resultSet.getString(7));
		// toReturn.setClub(this.resultSet.getString(8));
		// toReturn.setGender(this.resultSet.getString(9));
		// toReturn.setLicense(this.resultSet.getBoolean(10));
		// toReturn.setCountryRating(this.resultSet.getBoolean(11));
		// toReturn.setBirthdate(this.resultSet.getString(12));
		// }
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }
		// return toReturn;
	}

	/**
	 * @param id
	 *            the unique DB ID
	 * @param person
	 *            the person data to be updatet. The existing person under the
	 *            ID will be completely overwritten.
	 * @throws SQLException
	 */
	public void update(int id, Person person) throws SQLException {
		this.psUpdate = this.connection.prepareStatement("update " + Constants.ARCHER_TABLENAME + " set id=?, name=?, surName=?, nation=?, country=?, "
				+ "bowCathegory=?, ageClass=?, club=?, gender=?, license=?, countryRating=?, birthDate=? where id=?");
		this.statements.add(this.psUpdate);

		this.psUpdate.setInt(1, id);
		this.psUpdate.setString(2, person.getName());
		this.psUpdate.setString(3, person.getSurName());
		this.psUpdate.setString(4, person.getNation());
		this.psUpdate.setString(5, person.getCountry());
		this.psUpdate.setString(6, person.getBowCathegory());
		this.psUpdate.setString(7, person.getAgeClass());
		this.psUpdate.setString(8, person.getClub());
		this.psUpdate.setString(9, person.getGender());
		this.psUpdate.setBoolean(10, person.isLicense());
		this.psUpdate.setBoolean(11, person.isCountryRating());
		this.psUpdate.setString(12, person.getBirthdate());
		this.psUpdate.setInt(13, id);
		this.psUpdate.executeUpdate();

		this.setUpToDate(false);

		this.setChanged();
		this.notifyObservers();
	}

	/**
	 * @return the latest intry in the person DB (== highest unique ID)
	 * @throws SQLException
	 */
	public int getLatestEntry() throws SQLException {
		this.resultSet = this.statement.executeQuery("SELECT id FROM " + Constants.ARCHER_TABLENAME + " ORDER BY id DESC");
		int toReturn = -1;
		if (this.resultSet.next()) {
			toReturn = this.resultSet.getInt(1);
		}
		return toReturn;
	}

	/**
	 * @param toDelete
	 *            the person's unique ID - person will be entirely deleted
	 * @throws SQLException
	 */
	public void deletePerson(int toDelete) throws SQLException {
		this.psDelete = this.connection.prepareStatement("delete from " + Constants.ARCHER_TABLENAME + " where id=?");
		this.statements.add(this.psDelete);
		this.psDelete.setInt(1, toDelete);
		this.psDelete.executeUpdate();

		this.setUpToDate(false);

		this.setChanged();
		this.notifyObservers();
	}

	/**
	 * @param person
	 *            the person to insert into the DB. The person will only be
	 *            added to the DB if it's not in there yet
	 * @return the corresponding unique ID
	 * @throws SQLException
	 */
	public int insertArcherWithoutNotify(Person person) throws SQLException {
		if (!this.personExistsAlready(person)) {
			try {
				/*
				 * checks for the next unique id
				 */
				this.resultSet = this.statement.executeQuery("SELECT id FROM " + Constants.ARCHER_TABLENAME + " ORDER BY id DESC");
				int id = 1;
				if (this.resultSet.next()) {
					id = this.resultSet.getInt(1) + 1;
				}

				this.psInsert = this.connection.prepareStatement("insert into " + Constants.ARCHER_TABLENAME + " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				this.statements.add(this.psInsert);

				this.psInsert.setInt(1, id);
				this.psInsert.setString(2, person.getName());
				this.psInsert.setString(3, person.getSurName());
				this.psInsert.setString(4, person.getNation());
				this.psInsert.setString(5, person.getCountry());
				this.psInsert.setString(6, person.getBowCathegory());
				this.psInsert.setString(7, person.getAgeClass());
				this.psInsert.setString(8, person.getClub());
				this.psInsert.setString(9, person.getGender());
				this.psInsert.setBoolean(10, person.isLicense());
				this.psInsert.setBoolean(11, person.isCountryRating());
				this.psInsert.setString(12, person.getBirthdate());
				this.psInsert.executeUpdate();

				/*
				 * We commit the transaction. Any changes will be persisted to
				 * the database now.
				 */
				this.connection.commit();
				this.setUpToDate(false);
				this.setChanged();
				return id;

			} catch (SQLException e) {
				e.printStackTrace();
			}
			return -1;
		} else {
			return this.getArcherID(person);
		}
	}

	/**
	 * @param person
	 *            the person to insert into the DB. The person will only be
	 *            added to the DB if it's not in there yet
	 * @return the corresponding unique ID
	 * @throws SQLException
	 */
	public int insertArcher(Person person) throws SQLException {
		int toReturn = this.insertArcherWithoutNotify(person);
		// if (!this.personExistsAlready(person)) {
		// try {
		// /*
		// * checks for the next unique id
		// */
		// this.resultSet = this.statement.executeQuery("SELECT id FROM " +
		// Constants.ARCHER_TABLENAME + " ORDER BY id DESC");
		// int id = 1;
		// if (this.resultSet.next()) {
		// id = this.resultSet.getInt(1) + 1;
		// }
		//
		// this.psInsert = this.connection.prepareStatement("insert into " +
		// Constants.ARCHER_TABLENAME +
		// " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		// this.statements.add(this.psInsert);
		//
		// this.psInsert.setInt(1, id);
		// this.psInsert.setString(2, person.getName());
		// this.psInsert.setString(3, person.getSurName());
		// this.psInsert.setString(4, person.getNation());
		// this.psInsert.setString(5, person.getCountry());
		// this.psInsert.setString(6, person.getBowCathegory());
		// this.psInsert.setString(7, person.getAgeClass());
		// this.psInsert.setString(8, person.getClub());
		// this.psInsert.setString(9, person.getGender());
		// this.psInsert.setBoolean(10, person.isLicense());
		// this.psInsert.setBoolean(11, person.isCountryRating());
		// this.psInsert.setString(12, person.getBirthdate());
		// this.psInsert.executeUpdate();
		//
		// /*
		// * We commit the transaction. Any changes will be persisted to
		// * the database now.
		// */
		// this.connection.commit();
		// this.setUpToDate(false);
		// this.setChanged();
		// this.notifyObservers();
		// return id;
		//
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }
		// return -1;
		// } else {
		// return this.getArcherID(person);
		// }
		this.setUpToDate(false);
		this.setChanged();
		this.notifyObservers();
		return toReturn;
	}

	/**
	 * Creates the color SQL-Table
	 */
	public void createColorTable() {
		try {
			this.statement.execute("create table " + Constants.COLOR_TABLENAME + "(bowCathegory varchar(40), ageClass varchar(40), gender varchar(40), red int, green int, blue int)");
		} catch (SQLException e) {
			// e.printStackTrace();
		}
	}

	/**
	 * SQL drop table color
	 * @throws SQLException
	 */
	public void deleteColorTable() throws SQLException {
		super.deleteTable(Constants.COLOR_TABLENAME);
	}

	/**
	 * @param bowCathegory
	 *            according bow cathegory
	 * @param ageClass
	 *            according age class
	 * @param gender
	 *            according gender
	 * @return the color saved to the DB according to the parameters
	 * @throws SQLException
	 */
	public Color getColor(String bowCathegory, String ageClass, String gender) throws SQLException {
		this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.COLOR_TABLENAME);
		while (this.resultSet.next()) {
			if (this.resultSet.getString(1).equals(bowCathegory)) {
				if (this.resultSet.getString(2).equals(ageClass)) {
					if (this.resultSet.getString(3).equals(gender)) {
						return new Color(this.resultSet.getInt(4), this.resultSet.getInt(5), this.resultSet.getInt(6));
					}
				}
			}
		}
		return new Color(255, 255, 255);
	}

	private boolean hasSavedColor(String bowCathegory, String ageClass, String gender) throws SQLException {
		this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.COLOR_TABLENAME);
		while (this.resultSet.next()) {
			if (this.resultSet.getString(1).equals(bowCathegory)) {
				if (this.resultSet.getString(2).equals(ageClass)) {
					if (this.resultSet.getString(3).equals(gender)) {
						return true;
					}
				}
			}
		}
		return false;
	}

	/**
	 * @param color
	 *            the color to save into the DB
	 * @param bowCathegory
	 *            according bow cathegory
	 * @param ageClass
	 *            according age class
	 * @param gender
	 *            according gender
	 * @throws SQLException
	 */
	public void setColor(Color color, String bowCathegory, String ageClass, String gender) throws SQLException {
		Color oldColor = this.getColor(bowCathegory, ageClass, gender);
		// if color has changed
		if (!oldColor.equals(color)) {
			if (this.hasSavedColor(bowCathegory, ageClass, gender)) {
				this.psUpdate = this.connection.prepareStatement("update " + Constants.COLOR_TABLENAME + " set red=?, green=?, blue=? WHERE bowCathegory=? AND ageClass=? AND gender=?");
				this.statements.add(this.psUpdate);

				this.psUpdate.setInt(1, color.getRed());
				this.psUpdate.setInt(2, color.getGreen());
				this.psUpdate.setInt(3, color.getBlue());
				this.psUpdate.setString(4, bowCathegory);
				this.psUpdate.setString(5, ageClass);
				this.psUpdate.setString(6, gender);
				this.psUpdate.executeUpdate();
			} else {
				this.psInsert = this.connection.prepareStatement("insert into " + Constants.COLOR_TABLENAME + " values (?, ?, ?, ?, ?, ?)");
				this.statements.add(this.psInsert);

				this.psInsert.setString(1, bowCathegory);
				this.psInsert.setString(2, ageClass);
				this.psInsert.setString(3, gender);
				this.psInsert.setInt(4, color.getRed());
				this.psInsert.setInt(5, color.getGreen());
				this.psInsert.setInt(6, color.getBlue());
				this.psInsert.executeUpdate();
			}

			this.setChanged();
			this.notifyObservers();
		}
	}

	/**
	 * @param id
	 *            the unique id in the DB
	 * @return [id, name, surName, nation, country, bowCathegory, ageClass,
	 *         club, gender, license, countryRating, birthdate]
	 */
	public Object [] getByIdToArray(int id) {
		Object [] tmp = this.getById(id).toArray();
		Object [] toReturn = new Object [tmp.length + 1];
		toReturn [0] = id;
		for (int i = 1; i < toReturn.length; i++) {
			toReturn [i] = tmp [i - 1];
		}
		return toReturn;
	}

	/**
	 * @return an array with a unique entry for any nation assigned in the DB
	 */
	public String [] getAllNations() {
		HashMap < Integer, Person > tmp = this.getAllArchers();
		LinkedList < String > toReturnList = new LinkedList < String >();
		for (int i = 1; i <= tmp.size(); i++) {
			if (tmp.containsKey(i)) {
				if (!toReturnList.contains(tmp.get(i).getNation())) {
					toReturnList.add(tmp.get(i).getNation());

				}
			}
		}
		String [] toReturnArray = new String [toReturnList.size()];
		int i = 0;
		while (toReturnList.size() > 0) {
			toReturnArray [i] = toReturnList.removeFirst();
			i++;
		}
		return toReturnArray;
	}

	/**
	 * @return an array with a unique entry for any country assigned in the DB
	 */
	public String [] getAllCountries() {
		HashMap < Integer, Person > tmp = this.getAllArchers();
		LinkedList < String > toReturnList = new LinkedList < String >();
		for (int i = 1; i <= tmp.size(); i++) {
			if (tmp.containsKey(i)) {
				if (!toReturnList.contains(tmp.get(i).getCountry())) {
					toReturnList.add(tmp.get(i).getCountry());

				}
			}
		}
		String [] toReturnArray = new String [toReturnList.size()];
		int i = 0;
		while (toReturnList.size() > 0) {
			toReturnArray [i] = toReturnList.removeFirst();
			i++;
		}
		return toReturnArray;
	}

	/**
	 * @return an array with a unique entry for any bow cathegory assigned in
	 *         the DB
	 */
	public String [] getAllBowCathegories() {
		HashMap < Integer, Person > tmp = this.getAllArchers();
		LinkedList < String > toReturnList = new LinkedList < String >();
		for (int i = 1; i <= tmp.size(); i++) {
			if (tmp.containsKey(i)) {
				if (!toReturnList.contains(tmp.get(i).getBowCathegory())) {
					toReturnList.add(tmp.get(i).getBowCathegory());

				}
			}
		}
		String [] toReturnArray = new String [toReturnList.size()];
		int i = 0;
		while (toReturnList.size() > 0) {
			toReturnArray [i] = toReturnList.removeFirst();
			i++;
		}
		return toReturnArray;
	}

	/**
	 * @return an array with a unique entry for any age class assigned in the DB
	 */
	public String [] getAllAgeClasses() {
		HashMap < Integer, Person > tmp = this.getAllArchers();
		LinkedList < String > toReturnList = new LinkedList < String >();
		for (int i = 1; i <= tmp.size(); i++) {
			if (tmp.containsKey(i)) {
				if (!toReturnList.contains(tmp.get(i).getAgeClass())) {
					toReturnList.add(tmp.get(i).getAgeClass());

				}
			}
		}
		String [] toReturnArray = new String [toReturnList.size()];
		int i = 0;
		while (toReturnList.size() > 0) {
			toReturnArray [i] = toReturnList.removeFirst();
			i++;
		}
		return toReturnArray;
	}

	/**
	 * @return an array with a unique entry for any club assigned in the DB
	 */
	public String [] getAllClubs() {
		HashMap < Integer, Person > tmp = this.getAllArchers();
		LinkedList < String > toReturnList = new LinkedList < String >();
		for (int i = 1; i <= tmp.size(); i++) {
			if (tmp.containsKey(i)) {
				if (!toReturnList.contains(tmp.get(i).getClub())) {
					toReturnList.add(tmp.get(i).getClub());

				}
			}
		}
		String [] toReturnArray = new String [toReturnList.size()];
		int i = 0;
		while (toReturnList.size() > 0) {
			toReturnArray [i] = toReturnList.removeFirst();
			i++;
		}
		return toReturnArray;
	}

	/**
	 * Creates the tournamentType SQL-Table
	 */
	public void createDistancesTable() {

		try {
			this.statement.execute("create table " + Constants.TOURNAMENt_TYPE_TABLENAME
					+ "(tournamentType varchar(40), bowCathegory varchar(40), ageClass varchar(40), gender varchar(40), distances varchar(300))");
		} catch (SQLException e) {
			// e.printStackTrace();
		}
	}

	/**
	 * SQL drop table tournamentType
	 * @throws SQLException
	 */
	public void deleteDistancesTable() throws SQLException {
		super.deleteTable(Constants.TOURNAMENt_TYPE_TABLENAME);
	}

	/**
	 * @param tournamentType
	 *            the tournament type name
	 * @param bowCathegory
	 *            the assigned bow cathegory
	 * @param ageClass
	 *            the assigned age class
	 * @param gender
	 *            the assigned gender
	 * @param distances
	 *            the different distances in the different rounds
	 * @throws SQLException
	 */
	public void setDistances(String tournamentType, String bowCathegory, String ageClass, String gender, LinkedList < Integer > distances) throws SQLException {
		if (this.containsDistancesData(tournamentType, bowCathegory, ageClass, gender)) {
			this.deleteDistanceEntry(tournamentType, bowCathegory, ageClass, gender);
		}
		this.psInsert = this.connection.prepareStatement("insert into " + Constants.TOURNAMENt_TYPE_TABLENAME + " values (?, ?, ?, ?, ?)");
		this.statements.add(this.psInsert);

		this.psInsert.setString(1, tournamentType);
		this.psInsert.setString(2, bowCathegory);
		this.psInsert.setString(3, ageClass);
		this.psInsert.setString(4, gender);
		this.psInsert.setString(5, this.listToString(distances));

		this.psInsert.executeUpdate();

		this.setChanged();
		this.notifyObservers();
	}

	/**
	 * @param tournamentType
	 *            the tournament type
	 * @param bowCathegory
	 *            the bow cathegory
	 * @param ageClass
	 *            the age class
	 * @param gender
	 *            the gender
	 * @return true if there exist distances for the given parameters, else
	 *         otherwise
	 * @throws SQLException
	 */
	public boolean containsDistancesData(String tournamentType, String bowCathegory, String ageClass, String gender) throws SQLException {
		this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.TOURNAMENt_TYPE_TABLENAME);
		while (this.resultSet.next()) {
			if (this.resultSet.getString(1).equals(tournamentType)) {
				if (this.resultSet.getString(2).equals(bowCathegory)) {
					if (this.resultSet.getString(3).equals(ageClass)) {
						if (this.resultSet.getString(4).equals(gender)) {
							return true;
						}
					}
				}
			}
		}
		return false;
	}

	/**
	 * @param tournamentType
	 *            according tournament type
	 * @param bowCathegory
	 *            according bow cathegory
	 * @param ageClass
	 *            according age class
	 * @param gender
	 *            according gender
	 * @return according distances in the different rounds for the given
	 *         parameters
	 * @throws SQLException
	 */
	public Integer [] getDistances(String tournamentType, String bowCathegory, String ageClass, String gender) throws SQLException {
		this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.TOURNAMENt_TYPE_TABLENAME);
		while (this.resultSet.next()) {
			if (this.resultSet.getString(1).equals(tournamentType)) {
				if (this.resultSet.getString(2).equals(bowCathegory)) {
					if (this.resultSet.getString(3).equals(ageClass)) {
						if (this.resultSet.getString(4).equals(gender)) {
							return this.stringToArray(this.resultSet.getString(5));
						}
					}
				}
			}
		}
		return new Integer [0];
	}

	public int getNumberOfRounds(String tournamentType, String bowCathegory, String ageClass, String gender) throws SQLException {
		return this.getDistances(tournamentType, bowCathegory, ageClass, gender).length;
	}

	public void saveTournamentType(String tournamentType) {
		String [] bowCathegories = this.getAllBowCathegories();
		String [] ageClasses = this.getAllAgeClasses();
		String [] genders = new String [] { Constants.MALE, Constants.FEMALE };

		for (int i = 0; i < bowCathegories.length; i++) {
			for (int j = 0; j < ageClasses.length; j++) {
				for (int k = 0; k < genders.length; k++) {
					try {
						if (!this.containsDistancesData(tournamentType, bowCathegories [i], ageClasses [j], genders [k])) {
							LinkedList < Integer > toInsert = new LinkedList < Integer >();
							toInsert.add(10);
							this.setDistances(tournamentType, bowCathegories [i], ageClasses [j], genders [k], toInsert);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}

	}

	/**
	 * @param string
	 *            a list of Integers separated by semicolons
	 * @return an array of Integers
	 */
	private Integer [] stringToArray(String string) {
		String [] tmp = string.split(";");
		Integer [] toReturn = new Integer [tmp.length];
		for (int i = 0; i < toReturn.length; i++) {
			toReturn [i] = Integer.parseInt(tmp [i]);
		}
		return toReturn;
	}

	/**
	 * @param toConvert
	 *            a LinkesList of type String
	 * @return the given List, separated by semicolons
	 */
	private String listToString(LinkedList < Integer > toConvert) {
		StringBuilder output = new StringBuilder();
		for (int i = 0; i < toConvert.size(); i++) {
			output.append(toConvert.get(i));
			output.append(";");
		}
		return output.toString();
	}

	/**
	 * @return an array with a unique entry for any tournament type assigned in
	 *         the DB
	 * @throws SQLException
	 */
	public String [] getAllTournamentTypes() throws SQLException {
		LinkedList < String > tournamentTypeList = new LinkedList < String >();
		this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.TOURNAMENt_TYPE_TABLENAME);
		while (this.resultSet.next()) {
			if (!tournamentTypeList.contains(this.resultSet.getString(1))) {
				tournamentTypeList.add(this.resultSet.getString(1));
			}
		}
		String [] toReturnArray = new String [tournamentTypeList.size()];
		int i = 0;
		while (tournamentTypeList.size() > 0) {
			toReturnArray [i] = tournamentTypeList.removeFirst();
			i++;
		}
		return toReturnArray;
	}

	/**
	 * @param toDelete
	 *            the person's unique ID - person will be entirely deleted
	 * @throws SQLException
	 */
	public void deleteDistanceEntry(String tournamentType, String bowCathegory, String ageClass, String gender) throws SQLException {
		this.psDelete = this.connection.prepareStatement("delete from " + Constants.TOURNAMENt_TYPE_TABLENAME + " where tournamentType=? AND bowCathegory=? AND ageClass=? AND gender=?");
		this.statements.add(this.psDelete);
		this.psDelete.setString(1, tournamentType);
		this.psDelete.setString(2, bowCathegory);
		this.psDelete.setString(3, ageClass);
		this.psDelete.setString(4, gender);
		this.psDelete.executeUpdate();
	}

	/**
	 * Creates the finalsPermissions SQL-Table
	 */
	public void createFinalsPermissionsTable() {
		try {
			this.statement.execute("create table " + Constants.FINALS_PERMISSIONS_TABLENAME + "(bowCathegory varchar(40), ageClass varchar(40), gender varchar(40), permission varchar(40))");
		} catch (SQLException e) {
			// e.printStackTrace();
		}
	}

	public void insertPermission(String bowCathegory, String ageClass, String gender, boolean hasPermission) throws SQLException {
		this.deletePermission(bowCathegory, ageClass, gender);

		this.psInsert = this.connection.prepareStatement("insert into " + Constants.FINALS_PERMISSIONS_TABLENAME + " values (?, ?, ?, ?)");
		this.statements.add(this.psInsert);

		this.psInsert.setString(1, bowCathegory);
		this.psInsert.setString(2, ageClass);
		this.psInsert.setString(3, gender);
		this.psInsert.setString(4, new Boolean(hasPermission).toString());

		this.psInsert.executeUpdate();

		this.setChanged();
		this.notifyObservers();
	}

	public void deletePermission(String bowCathegory, String ageClass, String gender) throws SQLException {
		this.psDelete = this.connection.prepareStatement("delete from " + Constants.FINALS_PERMISSIONS_TABLENAME + " where bowCathegory=? AND ageClass=? AND gender=?");
		this.statements.add(this.psDelete);
		this.psDelete.setString(1, bowCathegory);
		this.psDelete.setString(2, ageClass);
		this.psDelete.setString(3, gender);
		this.psDelete.executeUpdate();
	}

	public boolean hasPermission(String bowCathegory, String ageClass, String gender) {
		try {
			this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.FINALS_PERMISSIONS_TABLENAME);
			while (this.resultSet.next()) {
				String dbBowCathegory = this.resultSet.getString(1);
				String dbAgeClass = this.resultSet.getString(2);
				String dbGender = this.resultSet.getString(3);
				boolean dbHasPermission = new Boolean(this.resultSet.getString(4));
				if (bowCathegory.equals(dbBowCathegory) && ageClass.equals(dbAgeClass) && gender.equals(dbGender)) {
					return dbHasPermission;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/** if this part is reached, the data is not yet in the db */
		try {
			this.insertPermission(bowCathegory, ageClass, gender, false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * SQL drop table archer
	 * @throws SQLException
	 */
	public void deleteFinalsPermissionsTable() throws SQLException {
		super.deleteTable(Constants.FINALS_PERMISSIONS_TABLENAME);
	}

	public int getPermissionEntries() {
		int entries = 0;

		try {
			this.resultSet = this.statement.executeQuery("SELECT * FROM " + Constants.FINALS_PERMISSIONS_TABLENAME);
			while (this.resultSet.next()) {
				entries++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return entries;
	}
}